package Database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Model.Customer;
import Model.SalesOrder;




public class DBSalesOrder implements IFDBSalesOrder{
    private  Connection con;
    /** Creates a new instance of SalesOrder */
    public DBSalesOrder() {
      con = DbConnection.getInstance().getDBcon();
    }    
    public ArrayList<SalesOrder> getAllSalesOrders()
    {
        return miscWhere("");
    }
    public SalesOrder findSalesOrder(int orderID)
    {   String wClause = "  orderID = " + orderID + "";
        return singleWhere(wClause);
    }
   
    public int insertSalesOrder(SalesOrder salesOrder) throws Exception { // call to get
	       // the next ssn
	       // number
    	
    	int rc = -1;


String query = "INSERT INTO salesOrder(date, amount, deliveryStatus, deliveryDate, orderID, customerID)  VALUES('" + salesOrder.getDate() + "'," + salesOrder.getAmount() + ",'" + salesOrder.getDeliveryStatus() + "','" + salesOrder.getDeliveryDate() + "'," + salesOrder.getOrderID() + "," + salesOrder.getCustomer().getId() + ");";
System.out.println( salesOrder.getDate());
System.out.println("insert : " + query);
try { // insert new employee + dependent
Statement stmt = con.createStatement();
stmt.setQueryTimeout(5);
rc = stmt.executeUpdate(query);
stmt.close();
}// end try
catch (SQLException ex) {
System.out.println("SalesOrder ikke oprettet");
throw new Exception("SalesOrder is not inserted correct");
}
return (rc);
}
    public int deleteSalesOrder(int id) {
	int rc = -1;
	PreparedStatement pstmt = null;
	String delete = "delete from SalesOrder where orderID = ?";
	System.out.println(delete);
	try {
	    pstmt = con.prepareStatement(delete);
	    pstmt.setInt(1, id);
	    rc = pstmt.executeUpdate();
	} catch (SQLException sqlE) {
	    System.out.println("SQL Error");
	    System.out.println(sqlE.getMessage());
	} catch (Exception e) {
	    e.getMessage();

	}
	return rc;
    }


    //find one
	private SalesOrder buildSalesOrder(ResultSet results)
    {   SalesOrder salesOrder = new SalesOrder();
        try{ // the columns from the table emplayee  are used
        	salesOrder.setOrderID(results.getInt("orderID"));
        	salesOrder.setDate(results.getString("date"));
        	salesOrder.setAmount(results.getInt("amount"));
        	salesOrder.setDeliveryStatus(results.getString("deliveryStatus"));
        	salesOrder.setDeliveryDate(results.getString("deliveryDate"));
        	salesOrder.setCustomer(new Customer(results.getInt("customerID")));
        }
       catch(Exception e)
       {
           System.out.println("error in building the salesOrder object");
       }
       return salesOrder;
    }
	 public int updateSalesOrder(SalesOrder order) {
		 SalesOrder salesOrder = order;
			int rc = -1;			
			String query = "UPDATE salesOrder SET " + "sDate =" + salesOrder.getDate() + ", " + " amount= " + salesOrder.getAmount()+ ", " + "deliveryStatus='" + "";
			System.out.println("Update query:" + query);
			try { // update employee
			    Statement stmt = con.createStatement();
			    stmt.setQueryTimeout(5);
			    rc = stmt.executeUpdate(query);

			    stmt.close();
			}// slut try
			catch (Exception ex) {
			    System.out.println("Update exception in customer db: " + ex);
			}
			return (rc);
		    }

	
	
	
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT sDate, amount, deliveryStatus, deliveryDate, orderID, customerID FROM salesOrder";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	private SalesOrder singleWhere(String wClause)
	{
		ResultSet results;
		SalesOrder salesOrder = new SalesOrder();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the salesOrder from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            salesOrder = buildSalesOrder(results);
                            //assocaition is to be build
                            stmt.close();
                            
                               //The supervisor and department is to be build as well
                                int id = salesOrder.getCustomer().getId();
                                Customer customer = new Customer(id);
                                salesOrder.setCustomer(customer);
                                
                                System.out.println("Supervisor is seleceted");
                               // here the department has to be selected as well
                           
                           
                            }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
      return salesOrder;
}
	private ArrayList<SalesOrder> miscWhere(String wClause)
	{
            ResultSet results;
	    ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	
		
	    String query =  buildQuery(wClause);
  
            try{
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 SalesOrder salesOrder = new SalesOrder();
		 salesOrder = buildSalesOrder(results);	
                 list.add(salesOrder);	
		}//end while
                 stmt.close();     
                     for(SalesOrder salesOrder : list){
                        int id = salesOrder.getCustomer().getId();
                        Customer customer = new Customer(id);
                        salesOrder.setCustomer(customer);
                        System.out.println("Customer is seleceted");
                       // here the department has to be selected as well
                     }
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
}
